;;save template button on main window
(defun insertdata_templates ()
  ;;These are built with the cl-sqlite package. Some functions return values, some do not.
  ;; -------------------------------
  ;;must have data in form  ;;todo: combine these with an OR
  ;;the if clause expects one clause if true. (if <test> <thendo> <elsedo>)
  ;;therefore, here I wrap multiple commands in a dotimes 1 loop.
  (if (string-equal (text *ENTRY_MAT1*) "") 
      (dotimes (n 1) (setf (text *status_label*)
			   (format nil "...Can't save - No data in first row for materials."))
	(user-info-report "No data in first row of Materials." 4223421)
	(return-from insertdata_templates))
      )
  (if (string-equal (text *top_e_formula*) "")
      (dotimes (n 1) (setf (text *status_label*)
			   (format nil "...Can't save - No formula name entered."))
	(user-info-report "No formula name entered." 4223422)
	(return-from insertdata_templates))
      )
  (setf *existing_formula* "T") ;;initialize
  ;;check if formula already exists, prompt user for confirmation
  (if (sqlite:execute-non-query *db* "select Formula from Templates_base where Formula = ?" (text *top_e_formula*));;returns T if found, NIL if not
      (setf *existing_formula* (format nil "~a" (ask-yesno "Formula already exists, are you sure you want to overwrite?"))))
  (if (string-equal *existing_formula* "NIL") ;;had to break this out to its own if. can't
      (return-from insertdata_templates)      ;;be nested in previous if 
      )
  (setf *dateforsql* (get-unix-time))
  (sqlite:execute-non-query *db* "insert into templates_base (Formula, Quantity, Customer, Timedateunix, Notes) values (?, ?, ?, ?, ?)" (text *top_e_formula*) (text *top_e_quantity*) (text *top_e_customer*) *dateforsql* (text *top_e_notes*))
  ;;had error where db comes up as read-only. restart program if this occurs.

  (block blk_template ;;this allows us to exit just the loop if a materials row is empty
    ;;hopefully they don't skip rows. that would end up with not all data...
    ;;on second thought, just store it all, so they don't lose anything... 
    ;;can delete later if needed, via sql.
    
    ;;Good way to test: printf these in the printout function, assigned to any button.
    (loop for x from 1 to 6 do
      (setf *MAT_LOOP_MAT*
	    (symbol-in-symbol *MAT_LOOP_MAT* "*ENTRY_MAT" (write-to-string x) "*"))
      (setf *MAT_LOOP_LBS*
	    (symbol-in-symbol *MAT_LOOP_LBS* "*ENTRY_LBS" (write-to-string x) "*"))
      (setf *MAT_LOOP_OZ*
	    (symbol-in-symbol *MAT_LOOP_OZ*  "*ENTRY_OZ"  (write-to-string x) "*")) 
      (setf *MAT_LOOP_GMS*
	    (symbol-in-symbol *MAT_LOOP_GMS* "*ENTRY_GMS" (write-to-string x) "*"))
      (setf *MAT_LOOP_INT*
	    (symbol-in-symbol *MAT_LOOP_INT* "*ENTRY_INT" (write-to-string x) "*"))
      ;; allow for blank rows... commented out below
      ;; (if (string-equal (text *ENTRY_MAT1*) "") ;;must have something in materials
      ;;	  (return-from blk_template)
      ;;	  )
      (sqlite:execute-non-query *db* "insert into templates_materials (Lbs, Oz, Grams, Materials, Internal, Timedateunix) values (?, ?, ?, ?, ?, ?)"
				(text (get-symbol-in-symbol *MAT_LOOP_LBS*))
				(text (get-symbol-in-symbol *MAT_LOOP_OZ*))
				(text (get-symbol-in-symbol *MAT_LOOP_GMS*))
				(text (get-symbol-in-symbol *MAT_LOOP_MAT*))
				(text (get-symbol-in-symbol *MAT_LOOP_INT*))
				*dateforsql*)
	  ))

  ;; delete any old data:
  ;;
  ;; using new timedateunix stamp *dateforsql* to find old entry
  ;; store in variable the old timestamp:
  ;; (select Timedateunix from Templates_base where Formula = "" and Timedateunix != newstamp;)
  ;; drop rows for both tables that are old timestamp
  ;; e.g. (delete from Templates_base      where Timedateunix = timestamptodelete;)
  ;; e.g. (delete from Templates_materials where Timedateunix = timestamptodelete;)
  (setf *oldtimestamp* (sqlite:execute-single *db* "select Timedateunix from Templates_base where Formula = ? and Timedateunix != ?;" (text *top_e_formula*) *dateforsql*))
  (sqlite:execute-non-query *db* "delete from Templates_base where Timedateunix = ? ;" *oldtimestamp*)
  (sqlite:execute-non-query *db* "delete from Templates_materials where Timedateunix = ? ;" *oldtimestamp*)
  
  ;; message user
  (setf (text *status_label*)
	(format nil "...The template has been saved to the db."))
  (user-info-report "Data Entered." 4020201)
  )
